package com.javaweb.dao.impl;

import com.javaweb.aop.TimeMeasure;
import com.javaweb.common.StringUtils;
import com.javaweb.dao.UserDao;
import com.javaweb.model.Role;
import com.javaweb.model.User;
import com.javaweb.service.dto.UserConditionDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/9/23 16:10
 */
@Repository
@Slf4j
public class UserDaoImpl implements UserDao {

    @Autowired
    private Connection conn ;
    @Autowired
    private JdbcTemplate jdbcTemplate ;
    @Override
    public Integer insert(User user) {

        /**
         * SQL 逻辑
         */
        String sql = "INSERT INTO tb_user (name, password, salt, create_time, gender, score, email, status) VALUES" +
                "(?, ?, ?, ? ,?, ?, ?, ?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, user.getName()) ;
            ps.setString(2, StringUtils.md5Encoder(user.getPassword(),user.getSalt())) ;
            ps.setDate(4, new Date(user.getCreateTime().getTime()));
            ps.setString(3, user.getSalt()) ;
            ps.setInt(5, user.getGender()) ;
            ps.setInt(6, user.getScore()) ;
            ps.setString(7, user.getEmail()); ;
            ps.setInt(8, user.getStatus()) ;
            return ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert user error: " + e.getMessage()) ;
        }
        return 0 ;
    }

    @Override
    public Integer insertUserRole(Integer userId, Integer roleId) {
        String sql = "INSERT INTO tb_user_role (user_id, role_id) VALUES (?,?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setInt(1, userId) ;
            ps.setInt(2, roleId) ;
            return ps.executeUpdate() ;
        }catch (SQLException e) {
            log.error("execute insertUserRole error: " + e.getMessage()) ;
        }
        return 0;
    }

    @Override
    public User selectOne(String name, String password) {
        String sql = "SELECT * FROM tb_user WHERE name=? and password=?" ;
        try (PreparedStatement ps = this.conn.prepareStatement(sql) ;
        ) {
            ps.setString(1, name) ;
            ps.setString(2, StringUtils.md5Encoder(password, StringUtils.getSalt(name))) ;
            ResultSet rs = ps.executeQuery() ;
            if (rs.next()) {
                User user = new User()
                        .setUserId(rs.getInt(1))
                        .setName(name)
                        .setPassword(password)
                        .setSalt(rs.getString(4))
                        .setCreateTime(rs.getDate(5))
                        .setGender(rs.getInt(6)) ;
                return user ;
            }
        } catch (SQLException e) {
            log.error(e.getMessage()) ;
        }
        return null;
    }

    @Override
    public List<User> selectByName(String name) {
        String sql = "SELECT t1.user_id,t1.`name`,t1.`password`,t1.salt,t1.create_time,t1.gender,t1.score,t2.role_id,t3.`name` roleName " +
                "FROM " +
                "tb_user t1 " +
                "LEFT JOIN tb_user_role t2 on t1.user_id = t2.user_id " +
                "LEFT JOIN tb_role t3 on t2.role_id = t3.role_id " +
                "where t1.`name` = ?";
        return jdbcTemplate.query(sql, new Object[]{name},
                rs -> {
                    Map<Integer, User> userMap = new HashMap<>();
                    while (rs.next()) {
                        Integer userId = rs.getInt("user_id");
                        User user = userMap.get(userId);
                        if (ObjectUtils.isEmpty(user)) {
                            user = new User();
                            user.setUserId(rs.getInt("user_id"));
                            user.setName(rs.getString("name"));
                            user.setGender(rs.getInt("gender"));
                            user.setPassword(rs.getString("password"));
                            user.setSalt(rs.getString("salt"));
                            user.setCreateTime(rs.getDate("create_time"));
                            user.setScore(rs.getInt("score"));
                            user.setRoles(new ArrayList<>());
                            userMap.put(userId, user);
                        }

                        Integer roleId = rs.getInt("role_id");
                        if (roleId != null) {
                            Role role = new Role();
                            role.setRoleId(rs.getInt("role_id"));
                            role.setName(rs.getString("roleName"));
                            user.getRoles().add(role);
                        }
                    }

                    return new ArrayList<>(userMap.values());
                });
    }

    @Override
    public List<User> selectByRole(String roleName) {
        String sql = "SELECT u.* FROM tb_role r " +
                                 " JOIN tb_user_role ur ON r.role_id = ur.role_id" +
                                 " JOIN tb_user u ON u.user_id = ur.user_id" +
                                "  WHERE r.name = ?" ;
        List<User> userList = new ArrayList<>() ;
        try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setString(1, roleName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User()
                        .setUserId(rs.getInt(1))
                        .setName(rs.getString(2))
                        .setPassword(rs.getString(3))
                        .setSalt(rs.getString(4))
                        .setCreateTime(rs.getDate(5))
                        .setGender(rs.getInt(6));
                userList.add(user);
            }
            return userList;
        } catch (SQLException e) {
            log.error("execute selectByRole error: " + e.getMessage());
        }

        return userList;
    }

    @TimeMeasure
    @Override
    public Integer selectCountAll() {
        String sql = "SELECT COUNT(*) FROM tb_user" ;
        try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ResultSet rs = ps.executeQuery() ;
            if (rs.next()) {
                return rs.getInt(1) ;
            }
        } catch (SQLException e) {
            log.error("selectCountAll error: " + e.getMessage()) ;
        }
        return 0;
    }

    @Override
    public Integer updateScore(Integer userId, int increased) {
        String sql = "UPDATE tb_user SET score = score + ? WHERE user_id = ?" ;
//        try (
//                PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
//            ps.setInt(1, increased) ;
//            ps.setInt(2, userId) ;
//            return ps.executeUpdate() ;
//        }catch (SQLException e) {
//            log.error("execute update user score: " + e.getMessage()) ;
//        }
        return jdbcTemplate.update(sql, new Object[]{increased, userId});
    }

    @Override
    public List<User> selectUsers(UserConditionDto userCondition, Integer pageNum, Integer pageSize) {
        String sql = "SELECT t1.user_id,t1.`name`,t1.`password`,t1.salt,t1.create_time,t1.gender,t1.score,t1.`status`,t1.email " +
                "FROM " +
                "tb_user t1 " +
                "where 1 = 1";

        Object[] objects = new Object[]{};
        if(userCondition.getId() != null){
            sql = sql + " and user_id = ?";
            Object[] newObjects = new Object[objects.length + 1];
            System.arraycopy(objects, 0, newObjects, 0, objects.length);
            newObjects[objects.length] = userCondition.getId();
            objects = newObjects;
        }
        if(userCondition.getUsername() != null && !userCondition.getUsername().equals("")){
            sql = sql + " and name = ?";
            Object[] newObjects = new Object[objects.length + 1];
            System.arraycopy(objects, 0, newObjects, 0, objects.length);
            newObjects[objects.length] = userCondition.getUsername();
            objects = newObjects;
        }
        if(userCondition.getEmail() != null && !userCondition.getEmail().equals("")){
            sql = sql + " and email = ?";
            Object[] newObjects = new Object[objects.length + 1];
            System.arraycopy(objects, 0, newObjects, 0, objects.length);
            newObjects[objects.length] = userCondition.getEmail();
            objects = newObjects;
        }
        if(userCondition.getStatus() != null ){
            sql = sql + " and status = ?";
            Object[] newObjects = new Object[objects.length + 1];
            System.arraycopy(objects, 0, newObjects, 0, objects.length);
            newObjects[objects.length] = userCondition.getStatus();
            objects = newObjects;
        }
        if(pageNum != null && pageSize != null){
            sql = sql + " limit ?,?";
            Object[] newObjects = new Object[objects.length + 2];
            System.arraycopy(objects, 0, newObjects, 0, objects.length);
            newObjects[objects.length] = (pageNum - 1) * pageSize;
            newObjects[objects.length + 1] = pageSize;
            objects = newObjects;
        }

        return jdbcTemplate.query(sql,objects,rs -> {
            Map<Integer, User> userMap = new HashMap<>();
            while(rs.next()){
                Integer userId = rs.getInt("user_id");
                User user = userMap.get(userId);
                if(ObjectUtils.isEmpty(user)){
                    user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setName(rs.getString("name"));
                    user.setGender(rs.getInt("gender"));
                    user.setPassword(rs.getString("password"));
                    user.setSalt(rs.getString("salt"));
                    user.setCreateTime(rs.getDate("create_time"));
                    user.setScore(rs.getInt("score"));
                    user.setEmail(rs.getString("email"));
                    user.setStatus(rs.getInt("status"));
                    user.setRoles(new ArrayList<>());
                    userMap.put(userId,user);
                }
            }
            return new ArrayList<>(userMap.values());
        });
    }

    @Override
    public Integer updateScoreDecrease(Integer userId, int decreased) {
        String sql = "UPDATE tb_user SET score = score - ? WHERE user_id = ?" ;
        return jdbcTemplate.update(sql,decreased,userId) ;
    }

    @Override
    public Integer updatePassword(Integer userId, String password) {
        String sql = "UPDATE tb_user SET password = ? WHERE user_id = ?" ;

        return jdbcTemplate.update(sql,new Object[]{StringUtils.md5Encoder(password,"123456"),userId});
    }
}
